﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Xml;
using System.Windows.Forms;

namespace HYI.SDBSERVER
{
    public class DBCache
    {
        public DBCache()
        {
            //比对版本是否要下载
            string path = System.Environment.CurrentDirectory + "\\cache";
            if (!Directory.Exists(path))//判断是否存在
            {
                Directory.CreateDirectory(path);//创建新路径
            }

            //doCache();

        }

        public void doCache()
        {
            //比对版本是否要下载
            DBSERVER myADO = new DBSERVER();

            //数据库版本
            String SQL = "select TABLENAME,VERSION from TABLEVERSION order by TABLENAME";
            DataTable myHead = myADO.query(SQL);

            //本地的版本
            string sFile = System.Environment.CurrentDirectory + "\\cache\\TABLEVERSION.xml";
            if (!File.Exists(sFile))
            {
                SQL = "select TABLENAME,0 VERSION from TABLEVERSION order by TABLENAME";
                DataTable myTemp = myADO.query(SQL);
                WriteToXml(myTemp, sFile);
            }
            DataTable myTABLEVERSION = ReadFromXml(sFile);

            //比对
            for (int i = 0; i < myHead.Rows.Count; i++)
            {
                string strTableName = myHead.Rows[i]["TABLENAME"].ToString().ToUpper();

                bool iDownLoad = true;
                string strFilter = "TABLENAME='{0}'";
                strFilter = string.Format(strFilter, strTableName);
                DataRow[] dr = myTABLEVERSION.Select(strFilter);
                if (dr.Length > 0)
                {
                    if (dr[0]["VERSION"].ToString() != myHead.Rows[i]["VERSION"].ToString())
                    {
                        Console.WriteLine("要下载:"+ strTableName);
                    }
                    else
                    {
                        iDownLoad = false;
                    }
                }

                if (iDownLoad)
                {
                    //需要下载
                    string SQL2 = string.Empty;
                    DataTable myQuery = new DataTable();
                    string sFileName = System.Environment.CurrentDirectory + "\\cache\\"+ strTableName + ".xml";
                    if (strTableName == "TS_COLUMNS")
                    {
                        //界面字典
                        SQL2 = "select * from ts_columns where 1=1 order by fColLength desc,fTableName,fColName";
                    }
                    else if (strTableName == "S_TS_DICT")
                    {
                        //数据字典
                        SQL2 = "select fDictNo,fDictName,fDictType from S_TS_DICT where 1=1 order by fDictType,fDictNo";
                    }
                    else if (strTableName == "AM_SYS_ROLE")
                    {
                        //角色资料
                        SQL2 = "select id,name from am_sys_role where 1=1 order by id";
                    }
                    else if (strTableName == "AM_SYS_USER")
                    {
                        //用户资料
                        SQL2 = "select id,username from am_sys_user where status=1 order by id";
                    }
                    else if (strTableName == "I_TS_STOCK")
                    {
                        //仓库资料
                        SQL2 = "select fstockno,fstockname from i_ts_stock where fpoint_state=1 order by fstockno";
                    }
                    else if (strTableName == "A_TS_POINT")
                    {
                        //实验室资料
                        SQL2 = "select fpoint_no,fpoint_name,fpoint_flag from a_ts_point where fpoint_state=1 order by fpoint_no";
                    }
                    else if (strTableName == "A_TS_LAYER")
                    {
                        //楼层资料
                        SQL2 = "select flayer_no,flayer_name from a_ts_layer where 1=1 order by flayer_no";
                    }
                    else if (strTableName == "S_TS_EQUIPMENT_STATE")
                    {
                        //设备状态字典
                        SQL2 = " select fstateno,fstatename from s_ts_equipment_state where 1=1 order by fstateno";
                    }
                    else if (strTableName == "I_TS_GOOD_CLASS")
                    {
                        //物料分类
                        SQL2 = " select fclassstno,fclassstname,flevel from i_ts_good_class where 1=1 order by fclassstno";
                    }
                    else if (strTableName == "S_TS_EQUIPMENT_DICT")
                    {
                        //设备类型
                        SQL2 = " select fdictno,fdictname from s_ts_equipment_dict where 1=1 order by fdictno";
                    }
                    else if (strTableName == "S_TS_AIRITEM")
                    {
                        //空气项目
                        SQL2 = " select fairitem,fairname from s_ts_airitem  where 1=1 order by fairitem";
                    }
                    else if (strTableName == "S_TS_DANGER_LEVEL")
                    {
                        //风险等级
                        SQL2 = " select flevelno,flevelname from s_ts_danger_level where 1=1 order by flevelno";
                    }
                    myQuery = myADO.query(SQL2);
                    WriteToXml(myQuery, sFileName);
                }
            }

            myADO.Dispose();

            //保存最新版本
            WriteToXml(myHead, sFile);
        }

        /// <summary>
        /// 将DataTable的内容写入到XML文件中
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="address">XML文件地址</param>
        public bool WriteToXml(DataTable dt, string address)
        {
            try
            {
                //如果文件DataTable.xml存在则直接删除
                if (File.Exists(address))
                {
                    File.Delete(address);
                }
                XmlTextWriter writer =
                 new XmlTextWriter(address, Encoding.GetEncoding("utf-8"));
                writer.Formatting = Formatting.Indented;
                //XML文档创建开始
                writer.WriteStartDocument();
                writer.WriteComment("DataTable: " + dt.TableName);
                writer.WriteStartElement("DataTable"); //DataTable开始
                writer.WriteAttributeString("TableName", dt.TableName);
                writer.WriteAttributeString("CountOfRows", dt.Rows.Count.ToString());
                writer.WriteAttributeString("CountOfColumns", dt.Columns.Count.ToString());
                writer.WriteStartElement("ClomunName", ""); //ColumnName开始
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    writer.WriteAttributeString(
                     "Column" + i.ToString(), dt.Columns[i].ColumnName);
                }
                writer.WriteEndElement(); //ColumnName结束
                                          //按行各行
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    writer.WriteStartElement("Row" + j.ToString(), "");
                    //打印各列
                    for (int k = 0; k < dt.Columns.Count; k++)
                    {
                        writer.WriteAttributeString(
                         "Column" + k.ToString(), dt.Rows[j][k].ToString());
                    }
                    writer.WriteEndElement();
                }
                writer.WriteEndElement(); //DataTable结束
                writer.WriteEndDocument();
                writer.Close();
                //XML文档创建结束
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// 从XML文件中读取一个DataTable
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="address">XML文件地址</param>
        /// <returns></returns>
        public DataTable ReadFromXml(string address)
        {
            DataTable dt = new DataTable();
            try
            {
                if (!File.Exists(address))
                {
                    throw new Exception("文件不存在!");
                }
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(address);
                XmlNode root = xmlDoc.SelectSingleNode("DataTable");
                //读取表名
                dt.TableName = ((XmlElement)root).GetAttribute("TableName");
                //Console.WriteLine("读取表名： {0}", dt.TableName);
                //读取行数
                int CountOfRows = 0;
                if (!int.TryParse(((XmlElement)root).
                 GetAttribute("CountOfRows").ToString(), out CountOfRows))
                {
                    throw new Exception("行数转换失败");
                }
                //读取列数
                int CountOfColumns = 0;
                if (!int.TryParse(((XmlElement)root).
                 GetAttribute("CountOfColumns").ToString(), out CountOfColumns))
                {
                    throw new Exception("列数转换失败");
                }
                //从第一行中读取记录的列名
                foreach (XmlAttribute xa in root.ChildNodes[0].Attributes)
                {
                    dt.Columns.Add(xa.Value);
                    //Console.WriteLine("建立列： {0}", xa.Value);
                }
                //从后面的行中读取行信息
                for (int i = 1; i < root.ChildNodes.Count; i++)
                {
                    string[] array = new string[root.ChildNodes[0].Attributes.Count];
                    for (int j = 0; j < array.Length; j++)
                    {
                        array[j] = root.ChildNodes[i].Attributes[j].Value.ToString();
                    }
                    dt.Rows.Add(array);
                    //Console.WriteLine("行插入成功");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return new DataTable();
            }
            return dt;
        }


        //控制GridView栏位显示
        public void doSetGridVIew(string sMenuId,string sUser, DataGridView myGridView)
        {
            //载入数据字典
            string sFile = System.Environment.CurrentDirectory + "\\cache\\TS_COLUMNS.xml";
            if (!File.Exists(sFile))
            {
                Console.WriteLine("文件不存在");
                return;
            }
            DBCache myCache = new DBCache();
            DataTable myTs_Dict = myCache.ReadFromXml(sFile);

            //fDictType在数据库中是一个number类型，因此我通过filterExpression传递进去的参数没有加入引号
            //遍历GridView 
            foreach (DataGridViewColumn col in myGridView.Columns)
            {
                string sName = col.Name;
                if (col.HeaderText == "")
                {
                    continue;
                }

                //string strFilter = "fTableName='" + sMenuId + "' and fTableDisplay='"+sUser+"' and fColName='" + sName + "'";
                string strFilter = "fTableName='" + sMenuId + "' and fColName='" + sName + "'";
                DataRow[] dr = myTs_Dict.Select(strFilter);
                if (dr.Length > 0)
                {
                    col.HeaderText = dr[0]["fColCaption"].ToString();
                    int iWidth = int.Parse(dr[0]["fColLength"].ToString());
                    int iControl = int.Parse(dr[0]["fControl"].ToString());
                    int iDispFmt = int.Parse(dr[0]["fDispFmt"].ToString());
                    col.Width = iWidth;
                    if (iWidth == 0)
                    {
                        col.Visible = false;
                    }

                    if (iControl == 0)
                    {
                        col.ReadOnly = true;
                    }

                    //设置栏位日期格式
                    if (iDispFmt == 1)
                    {
                        col.DefaultCellStyle.Format = "yyyy-MM-dd";
                    }else if (iDispFmt == 2)
                    {
                        col.DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
                    }
                    col.Tag = iDispFmt;
                }
                else
                {
                    //col.Visible = false;
                    col.Width = 121;
                }
            }
        }

        //读取数据字典
        public DataTable doGetDict(string sDictName,string sFilter)
        {
            DataTable newdt = new DataTable();

            string sFile = System.Environment.CurrentDirectory + "\\cache\\"+ sDictName +".xml";
            if (!File.Exists(sFile))
            {
                Console.WriteLine("文件不存在");
                return newdt;
            }

            DataTable myTs_Dict = ReadFromXml(sFile);
            newdt = myTs_Dict.Clone();

            //fDictType在数据库中是一个number类型，因此我通过filterExpression传递进去的参数没有加入引号
            //string strFilter = "fDictType='{0}'";
            //string strFilter = string.Format(sFilter, sFilterValue);
            DataRow[] dr = myTs_Dict.Select(sFilter);
            for (int i = 0; i < dr.Length; i++)
            {
                newdt.ImportRow((DataRow)dr[i]);
            }

            return newdt;
        }

    }


}
